"""
Case Type   : ODBC/PyDriver连接兼容B库
Case Name   : 公共函数
Create At   : 2024/1/12
Owner       : z30056528
Description :
    1.公共函数
Expect      :
    1.公共函数
History     :
"""
import difflib
import json
import os
import re
import subprocess
import tempfile

from openGaussBase.testcase.utils.CommonSH import CommonSH


class ComODBC(CommonSH):
    def __init__(self, node_name='PrimaryDbUser'):
        super(ComODBC, self).__init__(node_name)
        self.mysql_f_name = None
        self.og_f_name = None
        self.mysql_c_name = None
        self.og_c_name = None
        self.contant = ''
        self.odbc_name = ''
        self.update_list = ['create', 'alter', 'drop', 'insert', 'update',
                            'delete']
        self.query_list = ['select', 'show']
        self.exec_list = ['create procedure', 'call']
        self.cur_ip = subprocess.getoutput(
            "hostname -I|head -1|awk '{print $1}'")
        self.err_ignore = [
            r'\[\d+\.\d+\.\d+\.\d+:\d+/\d+\.\d+\.\d+\.\d+:\d+\]',
            r'\*+ \d+,\d+ \*+', r'-+ \d+,\d+ -+']
        self.og_owned_param = ['dolphin.b_compatibility_mode',
                               'bytea_output',
                               'enable_opfusion',
                               'enable_beta_opfusion',
                               'enable_bitmapscan',
                               'enable_seqscan',
                               'extra_float_digits']
        self.err_ignore.extend([f'.*set\\s+{i}.*?\\n.*SQL SUCCESS\\n'
                                for i in self.og_owned_param])
        self.no_part_pattern = ['create procedure.*?begin.*?end;',
                                'create trigger.*?on.*?for each row.*?end;']

    @staticmethod
    def read_sql_file(f_path):
        if os.path.exists(f_path):
            with open(f_path, 'r', encoding='utf-8', errors='ignore') as f:
                contant = f.read()
        else:
            raise Exception('文件不存在，请检查！')
        return contant

    def exec_sql(self, sql, cursor):
        res = ''
        if sql.startswith('--'):
            res += sql
        elif re.search('|'.join(self.query_list), sql.lower()) and not \
                re.search('|'.join(self.update_list), sql.lower()) and not \
                re.search('|'.join(self.exec_list), sql.lower()):
            cursor.execute(sql)
            fetch_data = cursor.fetchall()
            data = [list(rows) for rows in fetch_data]
            res += json.dumps(data)
        elif re.search('|'.join(self.update_list), sql.lower()) and not \
                re.search('|'.join(self.exec_list), sql.lower()):
            cursor.execute(sql)
        else:
            cursor.execute(sql)
        return res

    def exec_sql_pyog(self, sql, cursor):
        # 格式化成java文件可识别sql语句
        res = ''
        try:
            if sql.startswith('--'):
                res += sql
            elif re.search('|'.join(self.query_list), sql.lower()) and not \
                    re.search('|'.join(self.update_list), sql.lower()) and not \
                    re.search('|'.join(self.exec_list), sql.lower()):
                b = list()
                for c in re.split('--.*\n', sql):
                    if c:
                        sql_res = cursor.prepare(c.strip())
                        for ros in sql_res():
                            data = list()
                            for a in ros:
                                a = str(a)
                                data.append(a)
                            b.append(data)
                self.log.info(f'==={b}')
                res = str(b)
            elif re.search('|'.join(self.update_list), sql.lower()) and not \
                    re.search('|'.join(self.exec_list), sql.lower()):
                cursor.execute(sql)
            else:
                cursor.execute(sql)
        except Exception as e:
            self.log.info(f'---pyog_error---')
            self.log.info(f'{sql}')
            self.log.info(f'{e}')
        return res

    def characteristic_sql(self, sql, *args):
        self.log.info('生成个性化sql')
        c_sql = sql.splitlines()
        for one in args:
            if one[1] < 0:
                index = len(c_sql) + one[1]
            else:
                index = one[1] - 1
            if one[0] == 'add':
                c_sql.insert(index, one[-1])
            elif one[0] == 'replace':
                c_sql[index] = one[-1]
            elif one[0] == 'delete':
                c_sql.pop(index)
            else:
                raise Exception(f'请检查入参：{one}')
        return "\n".join(c_sql)

    def format_case_sql(self, sql):
        comment = re.findall('--.*\n', sql, re.I)
        regex_res = re.split('|'.join(comment), sql)
        new_sql = ''
        for i in regex_res:
            if i and i.strip():
                new_sql += i.rstrip().replace('"', '\\"') + '\n'
        self.log.info(new_sql)
        return new_sql

    def part_sql_to_block(self, sql, cursor, flag, is_part=True):
        if not is_part:
            if '\n' not in sql:
                res = self.demo(sql, cursor, flag)
            else:
                tmp = f' ' + ''.join([line.strip() for line in
                                      sql.strip().splitlines()])
                res = self.demo(tmp, cursor, flag)
        else:
            res = ''
            for one in sql.strip().split(';'):
                if one and one.strip():
                    if '\n' not in one:
                        res += self.demo(one, cursor, flag)
                    else:
                        tmp = f' ' + ''.join([line.strip() + ' ' for line in
                                              one.strip().splitlines()])
                        res += self.demo(tmp, cursor, flag)
        return res

    @staticmethod
    def part_sql_to_step(sql):
        steps = list()
        pattern = u"[\u4e00-\u9fa5]+"
        tmp_sql = re.split('--', sql)
        for one in tmp_sql:
            if one:
                one = one.replace('"', '\\"') + '\n'
                if one.strip().startswith('@') or \
                        one.strip().startswith('step') or \
                        re.match(pattern, one.strip()):
                    inner_tmp = '--' + one.strip()
                    if inner_tmp.count('--') == len(inner_tmp.splitlines()):
                        steps.append([inner_tmp, 'comment'])
                    else:
                        mid = len(re.match('--.*?\n', inner_tmp).group())
                        steps.append([inner_tmp[:mid].strip(), 'comment'])
                        sub_sqls = inner_tmp[mid:].split(";\n")
                        for sub in sub_sqls:
                            steps.append([sub, 'sql'])
                else:
                    inner_tmp = one.strip()
                    steps.append([inner_tmp, 'sql'])
        return steps

    def part_sql_to_segment(self, sql):
        segments = list()
        pattern = [f'({i})' for i in self.no_part_pattern]
        regex_res = re.findall(r'|'.join(pattern), sql, re.I | re.S)
        if len(regex_res) == 0:
            segments.append([sql.strip(), 'part'])
        else:
            for one in regex_res:
                for inner in one:
                    self.log.info(inner)
                    if inner.strip():
                        tmp = sql.find(inner)
                        if tmp == -1:
                            # '没找到匹配项'
                            end = len(sql)
                            segments.append([sql[:end].strip(), 'part'])
                        else:
                            if tmp == 0:
                                # '从第一个字符开始匹配'
                                end = len(inner)
                                segments.append([sql[:end].strip(), 'no_part'])
                            else:
                                # '从中间位置开始匹配'
                                end = tmp + len(inner)
                                segments.append([sql[:tmp].strip(), 'part'])
                                segments.append([sql[tmp:end].strip(),
                                                 'no_part'])
                        sql = sql[end:]
            if sql != segments[-1]:
                segments.append([sql.strip(), 'part'])
        return segments

    def exec_odbc_sql(self, sql, cursor, flag):
        res = ''
        f_steps = self.part_sql_to_step(sql)
        for step in f_steps:
            if step[1] == 'comment':
                res += self.demo(sql, cursor, flag)
            else:
                f_segments = self.part_sql_to_segment(step[0])
                for segment in f_segments:
                    if segment[1] == 'part':
                        res += self.part_sql_to_block(segment[0], cursor, flag,
                                                      is_part=True)
                    else:
                        res += self.part_sql_to_block(segment[0], cursor, flag,
                                                      is_part=False)
        return res

    def demo(self, sql, cursor, flag):
        if flag == 1:
            res = self.exec_sql_pyog(sql, cursor)
        else:
            res = self.exec_sql(sql, cursor)

        return res

    def compare_sql_res(self, from_str, to_str, from_name, to_name):
        diff = difflib.context_diff(from_str.splitlines(),
                                    to_str.splitlines(),
                                    fromfile=from_name,
                                    tofile=to_name)
        str_diff = '\n'.join([i.strip() for i in diff])
        self.log.info('\n' + str_diff)
        return str_diff

    def compare_sql_err_res(self, from_str, to_str, from_name, to_name,
                            *ignore):
        self.log.info('-----忽略不重要信息-----')
        self.err_ignore.extend(ignore)
        for pattern in self.err_ignore:
            regex_res1 = re.sub(pattern, "", from_str)
            regex_res2 = re.sub(pattern, "", to_str)
            from_str = regex_res1
            to_str = regex_res2

        str_diff = self.compare_sql_res(from_str, to_str, from_name, to_name)
        return str_diff

    def remove_c_file(self):
        remove_list = list()
        if self.mysql_f_name:
            remove_list.append(f'{self.mysql_f_name}.java')
            remove_list.append(f'{self.mysql_f_name}.class')
        if self.og_f_name:
            remove_list.append(f'{self.og_f_name}.java')
            remove_list.append(f'{self.og_f_name}.class')
        if self.og_c_name:
            remove_list.append(self.og_jar_name)
        if self.mysql_c_name:
            remove_list.append(self.mysql_jar_name)
        for f in remove_list:
            f_path = os.path.join(os.getcwd(), f)
            if os.path.exists(f_path):
                os.remove(f_path)

    def getoutput(self, cmd, ispwd=False, is_print=True):
        if not ispwd:
            self.log.info(f'开始执行: {cmd.strip()}')
        f_out = None
        f_err = None
        tem_out = tempfile.TemporaryFile()
        tem_err = tempfile.TemporaryFile()
        with tem_out, tem_err:
            sub_obj = subprocess.Popen(cmd,
                                       shell=True,
                                       encoding="utf-8",
                                       stdout=tem_out,
                                       stderr=tem_err)
            sub_obj.communicate()
            tem_out.seek(0)
            f_out = tem_out.read().decode('utf-8')
            tem_err.seek(0)
            f_err = tem_err.read().decode('utf-8')
            if f_err:
                self.log.error(f'Error: {f_err}')
            if is_print:
                self.log.info(f'Success: {f_out}')
            return f_out.strip(), f_err.strip()
